/* Dwarf_Load.sql */

insert into ejb_mountain (mountain_id,mountain_name,location)
	values (nextval('ejb_mountain_mountain_id_seq'),
	'Moria','Misty Mountains');

insert into ejb_mountain (mountain_id,mountain_name,location)
	values (nextval('ejb_mountain_mountain_id_seq'),
	'Erebor','Rhovanion');

insert into ejb_mountain (mountain_id,mountain_name,location)
	values (nextval('ejb_mountain_mountain_id_seq'),
	'Iron Hills','Rhovanion');

insert into ejb_dwarf (dwarf_id,dwarf_name,born)
	values (nextval('ejb_dwarf_dwarf_id_seq'),'Durin',1731);

insert into ejb_dwarf (dwarf_id,dwarf_name,born)
	values (nextval('ejb_dwarf_dwarf_id_seq'),'Nain',1832);

insert into ejb_dwarf (dwarf_id,dwarf_name,born)
	values (nextval('ejb_dwarf_dwarf_id_seq'),'Thrain',1934);

insert into ejb_dwarf (dwarf_id,dwarf_name,born)
	values (nextval('ejb_dwarf_dwarf_id_seq'),'Thorin',2035);

update ejb_mountain set king_id = 
	(select dwarf_id from ejb_dwarf where dwarf_name = 'Durin')
	where mountain_name = 'Moria';

update ejb_dwarf set home_id =
	(select mountain_id from ejb_mountain where mountain_name = 'Moria')
	where home_id is null;

insert into ejb_dwarf (dwarf_id,dwarf_name,born)
	values (nextval('ejb_dwarf_dwarf_id_seq'),'Gloin',2136);

insert into ejb_dwarf (dwarf_id,dwarf_name,born)
	values (nextval('ejb_dwarf_dwarf_id_seq'),'Oin',2238);

insert into ejb_dwarf (dwarf_id,dwarf_name,born)
	values (nextval('ejb_dwarf_dwarf_id_seq'),'Dain',2440);

insert into ejb_dwarf (dwarf_id,dwarf_name,born)
	values (nextval('ejb_dwarf_dwarf_id_seq'),'DainWife',2450);

update ejb_mountain set king_id = 
	(select dwarf_id from ejb_dwarf where dwarf_name = 'Dain')
	where mountain_name = 'Erebor';

update ejb_dwarf set home_id =
	(select mountain_id from ejb_mountain where mountain_name = 'Erebor')
	where home_id is null;

update ejb_dwarf set spouse_id = 
	(select dwarf_id from ejb_dwarf where dwarf_name = 'Dain')
	where dwarf_name = 'DainWife';

update ejb_dwarf set spouse_id = 
	(select dwarf_id from ejb_dwarf where dwarf_name = 'DainWife')
	where dwarf_name = 'Dain';

insert into ejb_visit(dwarf_id,mountain_id)
	select dwarf_id, home_id from ejb_dwarf;

insert into ejb_visit (dwarf_id, mountain_id)
	values (1,2);
insert into ejb_visit (dwarf_id, mountain_id)
	values (1,3);
insert into ejb_visit (dwarf_id, mountain_id)
	values (3,3);
insert into ejb_visit (dwarf_id, mountain_id)
	values (5,3);